﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using iTelluro.Explorer.Infrastructure.Data.MainBoundedContext.UnitOfWork;
using iTelluro.Explorer.Infrastructure.Data.Seedwork;


namespace iTelluro.Explorer.Infrastructure.Data.MainBoundedContext
{
    public class IntricateSQLDataObj : IIntricateDataObj
    {
        protected SqlConnection _conn;
        private string _connStr;
        DbContext context;
        public IntricateSQLDataObj(IMainModuleUnitOfWork uow)
        {
            context = uow as DbContext;
            _connStr = context.Database.Connection.ConnectionString;
            _conn = new SqlConnection(_connStr);
        }
        #region IIntricateDataObj 成员
        public void CloseConnection()
        {
            this._conn.Close();
        }
        public void OpenConnection()
        {
            this._conn.Open();
        }
        public System.Data.IDataReader ExecuteReader(string sql, params object[] parameters)
        {
            SqlCommand cmd = new SqlCommand(sql, _conn);
            _conn.Open();
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters);
            }
            cmd.CommandType = CommandType.Text;
            IDataReader idr = cmd.ExecuteReader();
            return idr;
        }

        public System.Data.DataSet ExecuteDataSet(string sql, params object[] parameters)
        {
            SqlCommand cmd = new SqlCommand(sql, _conn);
            _conn.Open();
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters);
            }
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            _conn.Close();
            return ds;
        }

        public object ExecuteScalar(string sql, params object[] parameters)
        {
            SqlCommand cmd = new SqlCommand(sql, _conn);
            _conn.Open();
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters);
            }
            cmd.CommandType = CommandType.Text;
            object obj = cmd.ExecuteScalar();
            _conn.Close();
            return obj;
        }
        public List<string> GetColumns(string tableName)
        {
            string sql = "select name from syscolumns where OBJECT_NAME(id) = '" + tableName + "'";
            List<string> lstCol = new List<string>();
            using (IDataReader rdr = ExecuteReader(sql))
            {
                while (rdr.Read())
                {
                    if (!lstCol.Contains(rdr[0].ToString()))
                    {
                        lstCol.Add(rdr[0].ToString());

                    }
                }
                rdr.Close();
                CloseConnection();
            }
            return lstCol;
        }
        public int ExecuteNonQuery(string sql, params object[] parameters)
        {
            SqlCommand cmd = new SqlCommand(sql, _conn);
            _conn.Open();
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters);
            }
            cmd.CommandType = CommandType.Text;
            int obj = cmd.ExecuteNonQuery();
            _conn.Close();
            return obj;
        }
        public string SubString(string s, int index, int length)
        {
            return "SUBSTRING(" + s + "," + index + "," + length + ")";
        }
        #endregion

        #region IIntricateDataObj 成员


        public string And()
        {
            return " + ";
        }

        /// <summary>
        /// 该格式满足:metadata=res://*/Model.GeoDisaster.csdl|res://*/Model.GeoDisaster.ssdl|res://*/Model.GeoDisaster.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=192.168.100.42;initial catalog=GeoDisasterAndMonitoringProduct5;user id=sa;password=SQL@infoearth!;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient
        /// </summary>
        /// <param name="initialStr"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        //private string GetDataFromString(string initialStr, string key)
        //{
        //    string result = string.Empty;
        //    int keyindex = initialStr.IndexOf(key);
        //    int startIndex = initialStr.IndexOf('=', keyindex + 1) + 1;
        //    int endIndex = initialStr.IndexOf(';', startIndex) - 1;
        //    if (startIndex < endIndex)
        //    {
        //        result = initialStr.Substring(startIndex, endIndex - startIndex + 1);
        //    }
        //    return result;
        //}

        #endregion


        public DataSet ExecuteDataSet(string[] sql, params object[] parameters)
        {
            _conn.Open();
            DataSet ds = new DataSet();
            if (sql != null)
            {
                for (int i = 0; i < sql.Length; i++)
                {
                    SqlCommand cmd = new SqlCommand(sql[i], _conn);
                    if (parameters != null)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    cmd.CommandType = CommandType.Text;
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataSet temp = new DataSet();
                    sda.Fill(temp);
                    DataTable dt = temp.Tables[0].Copy();
                    dt.TableName = "table" + i;
                    ds.Tables.Add(dt);
                }
            }
            _conn.Close();
            return ds;
        }

        #region IIntricateDataObj 成员


        public void SetDBConfiguration(string subSysType)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region IBulkInsert 成员

        public void BulkInsert(DataTable table)
        {
            //var context = unitOfWork as DbContext;
            //var dbType = context.Database.Connection.ToString();
            //// if (dbType == "System.Data.SqlClient.SqlConnection")//SQL数据库
            //using (var reader = entities.ToDataTable(tableName))
            if (table.Rows.Count > 0)
            {
                using (var sqlBulkCopy = new SqlBulkCopy(_connStr))
                {
                    sqlBulkCopy.BulkCopyTimeout = 30;
                    sqlBulkCopy.BatchSize = 5000;
                    sqlBulkCopy.DestinationTableName = table.TableName;
                    sqlBulkCopy.NotifyAfter = 1000;
                    sqlBulkCopy.WriteToServer(table);
                }
            }
        }

        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            if (_conn.State == ConnectionState.Open)
            {
                _conn.Close();
            }
            _conn.Dispose();
            context.Dispose();
        }

        #endregion
    }
}
